package bigdata.backend.datasource.controller;

import bigdata.backend.datasource.entity.Datasource;
import bigdata.backend.datasource.service.DatasourceService;
import bigdata.backend.datasource.service.SchemaSyncService;
import bigdata.backend.datasource.utils.SchemaSyncException;
import bigdata.backend.datasource.utils.SyncTableResult;
import bigdata.backend.utils.Result;
import bigdata.backend.utils.ResultCodeEnum;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import java.util.List;
import java.util.Map;

/**
 * 数据源管理控制器
 * <p>
 * 提供以下能力：
 * - 数据源 CURD（仅元数据）
 * - 动态注册/注销数据源（连接池管理）
 * - 元数据查询（表列表、字段、建表语句）
 * - 安全 SQL 执行（SELECT / DML / DDL 分离）
 * - 表结构同步（支持同构/异构）
 * - 生成建表 DDL（供前端预览）
 * <p>
 * 所有接口均返回统一格式 {@link Result}
 */
@RestController
@RequestMapping("/api/datasource")
public class DatasourceController {

    @Autowired
    private DatasourceService datasourceService;

    @Autowired
    private SchemaSyncService schemaSyncService;

    // ========== 数据源元数据管理 ==========

    /**
     * 查询所有数据源（仅元数据，不含密码）
     */
    @GetMapping
    public Result<List<Datasource>> listAll() {
        List<Datasource> list = datasourceService.list();
        return Result.ok(list);
    }

    /**
     * 根据 ID 获取单个数据源
     */
    @GetMapping("/{id}")
    public Result<Datasource> getById(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        return Result.ok(ds);
    }

    /**
     * 新增数据源（仅保存元数据，不注册连接）
     */
    @PostMapping
    public Result<String> create(@Valid @RequestBody Datasource datasource) {
        datasourceService.save(datasource);
        return Result.ok("数据源创建成功");
    }

    /**
     * 删除数据源（逻辑删除 + 从动态池移除）
     */
    @DeleteMapping("/{id}")
    public Result<String> delete(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            datasourceService.unregisterDataSource(ds.getDbAlias());
        } catch (Exception ignored) {
            // 忽略移除失败（可能未注册）
        }
        datasourceService.removeById(id);
        return Result.ok("数据源删除成功");
    }


    /**
     * 更新数据源（仅元数据）
     * <p>
     * 注意：如果连接信息（host/port/user/pass等）变更，
     * 会自动注销旧连接，下次使用时用新配置重建。
     */
    @PutMapping("/{id}")
    public Result<String> update(@PathVariable String id, @Valid @RequestBody Datasource datasource) {
        // 确保 ID 一致（防止前端传错）
        if (!id.equals(datasource.getId().toString())) {
            return Result.<String>build(null, ResultCodeEnum.BAD_REQUEST).message("路径ID与请求体ID不一致");
        }

        Datasource existing = datasourceService.getById(id);
        if (existing == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        // MyBatis-Plus 的默认 updateById() 会忽略值为 null 的字段，不会生成 SET column = NULL
        boolean updated = datasourceService.updateByIdIncludingNulls(datasource);
        if (updated) {
            return Result.ok("数据源更新成功");
        } else {
            return Result.<String>build(null, ResultCodeEnum.INTERNAL_ERROR).message("更新失败");
        }
    }

    // ========== 动态连接管理 ==========

    /**
     * 手动注册数据源到连接池（用于测试连接）
     */
    @PostMapping("/{id}/register")
    public Result<String> register(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            datasourceService.registerDataSource(ds);
            return Result.ok("数据源注册成功");
        } catch (Exception e) {
            return Result.<String>build(null, ResultCodeEnum.DATASOURCE_REGISTER_FAILED).message("注册失败: " + e.getMessage());
        }
    }

    /**
     * 手动从连接池移除数据源
     */
    @PostMapping("/{id}/unregister")
    public Result<String> unregister(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            datasourceService.unregisterDataSource(ds.getDbAlias());
            return Result.ok("数据源已移除");
        } catch (Exception e) {
            return Result.<String>build(null, ResultCodeEnum.DATASOURCE_UNREGISTER_FAILED)
                    .message("移除失败: " + e.getMessage());
        }
    }

    /**
     * 根据数据源 ID 测试连接连通性
     * <p>
     * 用途：
     * - 运维手动检测
     * - 定时健康检查
     * - 配置变更后验证
     * <p>
     * 注意：此操作使用数据库中当前最新配置，不依赖内存中是否已注册
     */
    @GetMapping("/{id}/test-connection")
    public Result<Boolean> testConnectionById(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }

        boolean reachable = datasourceService.testConnectionById(id);
        if (reachable) {
            return Result.ok(true).message("连接成功");
        } else {
            return Result.build(false, ResultCodeEnum.BAD_REQUEST).message("连接失败，请检查网络、账号、密码或数据库服务状态");
        }
    }

    /**
     * 测试数据源连接是否可用
     */
    @PostMapping("/test-connection")
    public Result<Boolean> testConnection(@RequestBody Datasource datasource) {
        boolean reachable = datasourceService.testConnection(datasource);
        if (reachable) {
            return Result.ok(true).message("连接成功");
        } else {
            return Result.build(false, ResultCodeEnum.BAD_REQUEST).message("连接失败，请检查配置");
        }
    }

    // ========== 元数据查询 ==========

    /**
     * 获取指定数据源下的所有表名（基于已保存的配置）
     */
    @GetMapping("/{id}/tables")
    public Result<List<String>> getTablesById(@PathVariable String id) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        List<String> tables = datasourceService.getAllTables(ds);
        return Result.ok(tables);
    }

    /**
     * 获取指定表的字段信息
     */
    @GetMapping("/{id}/tables/{tableName}/columns")
    public Result<List<Map<String, Object>>> getColumnsById(
            @PathVariable String id,
            @PathVariable String tableName) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        List<Map<String, Object>> columns = datasourceService.getTableColumns(ds, tableName);
        return Result.ok(columns);
    }

    /**
     * 获取建表语句（SHOW CREATE TABLE）
     */
    @GetMapping("/{id}/tables/{tableName}/create-table-sql")
    public Result<String> getCreateTableSqlById(
            @PathVariable String id,
            @PathVariable String tableName) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        String sql = datasourceService.getCreateTableSql(ds, tableName);
        return Result.ok(sql);
    }

    /**
     * 获取表注释
     */
    @GetMapping("/{id}/tables/{tableName}/comment")
    public Result<String> getTableCommentById(
            @PathVariable String id,
            @PathVariable String tableName) {
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        String comment = datasourceService.getTableComment(ds, tableName);
        return Result.ok(comment);
    }

    // ========== SQL 执行 ==========

    /**
     * 执行 SELECT 查询（仅允许 SELECT）
     */
    @PostMapping("/{id}/query")
    public Result<List<Map<String, Object>>> executeQueryById(
            @PathVariable String id,
            @RequestBody Map<String, String> request) {
        String sql = request.get("sql");
        if (sql == null || sql.trim().isEmpty()) {
            return Result.<List<Map<String, Object>>>build(null, ResultCodeEnum.BAD_REQUEST).message("SQL 不能为空");
        }
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            List<Map<String, Object>> result = datasourceService.executeQuery(ds, sql);
            return Result.ok(result);
        } catch (IllegalArgumentException e) {
            return Result.<List<Map<String, Object>>>build(null, ResultCodeEnum.SQL_NOT_SELECT).message(e.getMessage());
        } catch (Exception e) {
            return Result.<List<Map<String, Object>>>build(null, ResultCodeEnum.SQL_EXECUTION_ERROR).message(e.getMessage());
        }
    }

    /**
     * 执行 DML（INSERT/UPDATE/DELETE）
     */
    @PostMapping("/{id}/dml")
    public Result<Integer> executeUpdateById(
            @PathVariable String id,
            @RequestBody Map<String, String> request) {
        String sql = request.get("sql");
        if (sql == null || sql.trim().isEmpty()) {
            return Result.<Integer>build(null, ResultCodeEnum.BAD_REQUEST).message("SQL 不能为空");
        }
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            int count = datasourceService.executeUpdate(ds, sql);
            return Result.ok(count);
        } catch (IllegalArgumentException e) {
            return Result.<Integer>build(null, ResultCodeEnum.SQL_NOT_DML).message(e.getMessage());
        } catch (Exception e) {
            return Result.<Integer>build(null, ResultCodeEnum.SQL_EXECUTION_ERROR).message(e.getMessage());
        }
    }

    /**
     * 执行 DDL（CREATE/ALTER/DROP/TRUNCATE/COMMENT）
     */
    @PostMapping("/{id}/ddl")
    public Result<String> executeDdlById(
            @PathVariable String id,
            @RequestBody Map<String, String> request) {
        String sql = request.get("sql");
        if (sql == null || sql.trim().isEmpty()) {
            return Result.<String>build(null, ResultCodeEnum.BAD_REQUEST).message("SQL 不能为空");
        }
        Datasource ds = datasourceService.getById(id);
        if (ds == null) {
            return Result.build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND);
        }
        try {
            datasourceService.executeDdl(ds, sql);
            return Result.ok("DDL 执行成功");
        } catch (IllegalArgumentException e) {
            return Result.<String>build(null, ResultCodeEnum.SQL_NOT_DDL).message(e.getMessage());
        } catch (Exception e) {
            return Result.<String>build(null, ResultCodeEnum.SQL_EXECUTION_ERROR).message(e.getMessage());
        }
    }

    // ========== 表结构同步 ==========

    /**
     * 同步表结构（源 → 目标）
     */
    @PostMapping("/sync-table")
    public Result<SyncTableResult> syncTable(
            @RequestParam String sourceId,
            @RequestParam String targetId,
            @RequestParam String sourceTable,
            @RequestParam(required = false) String targetTable) {

        Datasource sourceDs = datasourceService.getById(sourceId);
        Datasource targetDs = datasourceService.getById(targetId);
        if (sourceDs == null) {
            return Result.<SyncTableResult>build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND).message("源数据源不存在");
        }
        if (targetDs == null) {
            return Result.<SyncTableResult>build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND).message("目标数据源不存在");
        }

        try {
            SyncTableResult result = schemaSyncService.syncTable(sourceDs, targetDs, sourceTable, targetTable);
            if (result.isSuccess()) {
                return Result.ok(result);
            } else {
                return Result.build(result, ResultCodeEnum.SCHEMA_SYNC_FAILED).message(result.getError());
            }
        } catch (SchemaSyncException e) {
            return Result.<SyncTableResult>build(null, ResultCodeEnum.SCHEMA_SYNC_FAILED).message(e.getMessage());
        } catch (Exception e) {
            return Result.<SyncTableResult>build(null, ResultCodeEnum.INTERNAL_ERROR).message("同步过程异常: " + e.getMessage());
        }
    }

    /**
     * 生成目标建表 DDL（不执行，供前端预览）
     */
    @PostMapping("/generate-ddl")
    public Result<String> generateDdl(
            @RequestParam String sourceId,
            @RequestParam String targetId,
            @RequestParam String sourceTable,
            @RequestParam(required = false) String targetTable) {

        Datasource sourceDs = datasourceService.getById(sourceId);
        Datasource targetDs = datasourceService.getById(targetId);
        if (sourceDs == null) {
            return Result.<String>build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND).message("源数据源不存在");
        }
        if (targetDs == null) {
            return Result.<String>build(null, ResultCodeEnum.DATASOURCE_NOT_FOUND).message("目标数据源不存在");
        }

        try {
            String ddl = schemaSyncService.generateCreateTableDdl(sourceDs, targetDs, sourceTable, targetTable);
            return Result.ok(ddl);
        } catch (SchemaSyncException e) {
            return Result.<String>build(null, ResultCodeEnum.DDL_GENERATE_FAILED).message(e.getMessage());
        } catch (Exception e) {
            return Result.<String>build(null, ResultCodeEnum.INTERNAL_ERROR).message("DDL 生成异常: " + e.getMessage());
        }
    }
}